1: Create the report in Vitalware

What kind of files are generated will depend on the Type of report you are creating:

• Vitalware 2.5 and earlier: report generation typically makes use of ODBC (Open Database Connectivity) and most report Types will generate one or more .csv files.
• Vitalware 3.0 onwards: it is also possible to create ADO reports which report directly to an ActiveX Data Objects (ADO) RecordSet object (creating a .xml file) rather than reporting to an ODBC data source (which creates one or more .csv files).

Note: Certain report Types do not generate .csv files, e.g. the Export XML Document and XML Document reports, each of which generates XML.

Object Database Connectivity (ODBC) reports

For most report Types, running a report in Vitalware will output data as two or more files:

1. A main .csv file. This will include all of the report data unless the report includes groups of fields or table of values.
2. A separate .csv file for:
   • Any group created using the Create Group button in the Report Properties box (see 2. Select the fields to include in the report: the Fields tab for details).
   • Any field that is a table of values. As we'll see, a field that is a table of values is identified in the Report Properties box by the table icon beside the field name.
3. An additional file, schema.ini, is created, containing details about each .csv file (what fields are included, their data type, formats, etc.).

Typically, these files are saved to:

C:\Documents and Settings\[your username]\Local Settings\Application Data\KESoftware\Reports\e[module name]

For example, a report run in the Parties module, will save a file called eparties.csv to:

C:\Documents and Settings\[your username]\Local Settings\Application Data\KESoftware\Reports\eparties

The .csv files contain the data that the third party report application uses to generate the report.

Tip: If Windows Explorer on your computer is configured to hide system files, this folder may not be visible. To display the Reports folder, select Tools>Folder Options>View in Windows Explorer and select Show Hidden Files and Folders. See How to locate and open the .csv file generated when a report is run for more details.

There may be situations in which you wish to manipulate the raw .csv data: in this case you could simply locate and open the .csv file in Microsoft Excel and manipulate the data as required.

Generally, however, you will want your data presented in a more complete report format. If a pre-defined report does not meet your needs, it will be necessary to design a new report using a third party tool appropriate to your reporting needs.

When the new report is created it must be saved to the Vitalware server so that Vitalware can open the .csv data in the appropriate reporting application (Crystal Reports Viewer, Microsoft Word, Excel, etc.).

Note: Any time that the report format is modified in the third party application, the changed report must be saved back to the Vitalware server, overwriting the previous version of the report. Until this is done, the old version of the report will be used to view data.

ActiveX Data Objects (ADO) reports

Vitalware's Reporting subsystem was modified with Vitalware 3.0 in order to bypass some filtering which occurs when a Crystal Report or Microsoft report (Excel, Word, etc.) is generated out of Vitalware. Bypassing the filtering improves report generation and performance.

Two new report formats were introduced that report directly to an ActiveX Data Objects (ADO) RecordSet object (producing a .xml file):

• Crystal ADO Report
• Microsoft ADO Report

Running these reports in Vitalware generates a .xml file with the data from the record set. The location of this file can vary, but typically it can be found in:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

For example, a report run in the Parties module, will save the xmldata file to:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

Note: It is important not to move the xmldata.xml file as this will cause problems when sharing the report with other users.

In Vitalware:

1. Search for or otherwise list a group of records on which to report.

   Note: If you are designing a Crystal ADO report, the records in your initial record set must have a value in each field to be included in the report. If not, the field name will not appear in the list of available columns. Once the report is defined, it does not matter if a record does not have values in every field included in the report.

2. Click Reports in the Tool bar to display the Reports box.
3. Click New in the Reports box to display the Report Properties box.
4. Enter a descriptive name for the Report in the top text field.

   As we'll see later, this title will display in the Reports box, from where it can be selected by authorised users to run the report.

5. Select a report type from the Type drop list (Crystal Report in this example).

   Note: When the report is run, a filter specific to the Type selected is applied to the data to prepare it for output. See How to create and run reports for details of all available report Types.

   The Report Type tab will now appear similar to:

   

1. In the Report Properties box, select the Fields tab.

   On the Fields tab we add the fields that we wish to include in the report.

2. Click Add to display the Report Fields box.

   The Report Fields box lists all fields in the current module, as well as fields in attached modules (an attachment field is indicated by the plus icon):

   

   Report Fields options and icons are:

   Options	Button	Description
   Show Prompts		Display the Vitalware field name as it displays in a module window.
   Show Column Names		Display the back-end field name.
   Attach Module		Display fields in attached modules that can be included in the report.
   Single field		Indicates that this field is a single field (i.e. holds one value).
   Multi-field table		Indicates that this field is a table of values.
   Expand		Indicates that this field is an attachment field in the current module. Select the icon to list fields in the targetAn attachment is made from one record to another. The record from which the attachment is initiated is the Primary record; the record that is attached is called the Target. (attached) module that can be included in your report.
   Multi-field table AND an attachment field		Indicates that this attachment field is also a table of values (see the Note to Table as Text below).
   Double-nested table AND an attachment field		Indicates that this attachment field is a double nested table (see the Note to Table as Text below).

3. In the Report Fields box, select a field and click Add

   -OR-

   Double-click a field.

   The field is added to the Fields list in the Report Properties box.

4. When all required fields are added, click Close in the Report Fields box.

   The Report Properties box now lists all the fields that will be included in our report:

   

   If we ran this report now, three .csv files would be generated: eorders.csv, AdmDateM.csv and AdmModif.csv. The last two of these contain detailswho modified the record (AdmModif.csv) and when it was modified (AdmDateM.csv), and it makes sense to group them in order that their values are stored in a single .csv file.

   How to group

   It is possible to sort and group fields within the Report Properties box in Vitalware as well as in third party applications such as Crystal Report Designer.

   If your report includes a very large number of records, it can be more efficient to perform sorting / grouping within Vitalware (using the Fields tab for grouping and the Sort Order tab for sorting) as this uses the processing power of the Vitalware server; when you set up a sort / group in Crystal Report Designer, you use the processing power of your PC to manipulate the records.

   Note: When you add fields to a group, a separate .csv file is generated for each group.

   The following fields have been added to our report:

   

   If we ran this report now, three .csv files would be generated: eorders.csv, AdmDateM.csv and AdmModif.csv. The last two of these contain details of who modified the record (AdmModif.csv) and when it was modified (AdmDateM.csv), and it makes sense to group them in order that their values are stored in a single .csv file:

   1. On the Fields tab, select Modified By: (Modification Details History).
   2. Click Create Group .

      A group called Group1 is created, containing Modified By: (Modification Details History).

   3. Select Modification Date: (Modification Details History) and click Add to Group .

      Modification Date: (Modification Details History) is added to Group1:

      

   Now when we run the report only two .csv files are generated: eorders.csv and Group1.csv (which contains values from Modified By and Modification Date).

   Additional Fields options and icons:

Options	Button	Description
Table as Text		When we run the report, data in the selected fields will be output as one or more .csv files: The main .csv file. A separate .csv file for: Any group created using the Create Group button in the Report Properties box (see below for details). Any field that is a table of values, e.g. Modified By in the example above. In the Report Properties box, a field that is a table of values is indicated by a table icon beside the field name. An additional file called schema.ini is created containing details about each .csv file (what fields are included, their data type, formats, etc.). In some circumstances it is desirable to simplify matters by including all the data in a single .csv file (as we've seen when we export data to a CSV format, for instance). To achieve this: Select the field name of the table of values (Modified By in this example). Click Table as Text . The fields in the table will now be included in the main .csv file rather than in a separate .csv file Tables When single value fields (Atomic fields) are added to a report, the data is placed directly into the base data file (e.g. eparties.csv). Atomic fields have a single line icon in front of the field name in the Report Fields box. Fields that can contain more than one value (generally represented by a Grid control) are called tables. These fields have the table icon in front of the field name in the Report Fields box. As a table may have more than one value for each record, the Vitalware export facility generates a separate file to contain the table data. In fact a separate file is generated for each table field in the report. The name of the separate file is the first eight letters of the column name appended with .csv. If required, use the Table as Text option on the Fields tab of the Report Properties box to include a table field's data in the base data file. This can be useful if your report is simple, however you won't then be able to perform more complex manipulations (such as grouping) on fields in the flattened table without writing some fairly complex formulas. Generally, if you are designing a report in a third party application such as Crystal Reports, you would not simplify the data structure in this way. Note: If a field you've added to the report is an attachment field AND a table of values (indicated by the two icons ) OR an attachment field AND a double nested table (indicated by the two icons ), it is not possible to include its data in the main .csv file using this method. To include fields like this in your report, it will generally be more appropriate to create a Crystal report.
Value as Text		Fields that contain partial dates are not recognised by Crystal Reports. To use a field that contains partial dates in a Crystal report: Select the field. Click . The data in the field will display as text.
Create Group and Add to Group	and	See How to group for details.
Remove		To remove a field from the Fields tab: Select the field. Click Remove.
Clear		To clear all fields from the Fields tab: Click Clear.

As we'll see when we reach the second stage of report creation, sorting and grouping fields can also be managed in the third party applications.

Note: It is not possible to group / sort fields of type Memo in Crystal Reports without first creating a formula. A Memo field is a text field over 255 characters in length. The Name: (Product Details) field in the Products module, for example, is a Crystal Memo field. In this case it may be simpler to group / sort in Vitalware rather than Crystal Reports.

Within Vitalware:

• Grouping of fields is handled on the Fields tab of the Report Properties box. See How to group in Vitalware for details.
• Sorting is handled on the Sort Order tab.

  Note: If your report includes a very large number of records, it can be more efficient to perform a sort within Vitalware as this uses the processing power of the Vitalware server to manipulate data; when you set up a sort / group in the Crystal Reports Designer, the processing power of your PC is used to perform the sort / group.
  However, if you create a group in the Crystal Reports Designer, sorting is automatically applied to the group (ascending or descending) and this overrides any sorting you applied in Vitalware. For that reason, if you wish to add a group to your report in the Crystal Reports Designer, there is little point sorting your records in Vitalware.

To define the sort order:

1. In the Report Properties box, select the Sort Order tab.

   All fields in the module are listed in the Fields list.

   Note: It is possible to sort your records on any field in the module, even if that field is not added to the report on the Fields tab.

2. Select a field in the Fields list and click Add to add it to the Order list

   -OR-

   Double-click the field name in the Fields list:

   

   Add as many fields on which to sort as required.

The options on the Options tab allow you to configure how records are sorted in the report.

Note: These options are only relevant if at least one field has been added to the Order list on the Sort Order tab.

1. In the Report Properties box, select the Options tab:

   

2. Accept or change the following Sort Options:

 Traveler's        Inn  

 Travelers Inn  

 Traveler's        Inn  

 Traveler's        Inn  

 Traveler's        Inn  

 Traveler's Inn  

On the Security tab we authorise users / groups to use the report by adding them to the Access list:

1. If required, select the Security tab to give other users permission to use this report.
2. Click OK.

   The new report is added to the Reports box.

In Vitalware, the next step in the creation of your report is to run it:

1. Select the new report in the Reports box.
2. Click Report All.

   Tip: You could click Report but this will only export data from the current or selected records to the file(s) you will next use to create the report in the third party application; the more data you include in the output files, the more you will have available to preview your report as you build it.

   What happens when you first run a report depends on the report type specified:

   • In all cases, filters appropriate to the Type are run on the data, preparing it for output.

     Note: Details of what files are created when a report is run and where the files are saved can be found here.

   • In most cases, a message displays indicating that your report does not exist on the server. This is to be expected as the report has not yet been built in the third party application (e.g. Crystal Reports):

     

   The following table summarises what you can expect to happen depending on the report Type:

Note: If you click No, the third party application will not open, but the output files will still be generated and copied to your computer.

In Step 2, we design the report in a third party application.